package com.example.qu_tao.database;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.util.Log;

import com.example.qu_tao.R;

import java.io.ByteArrayOutputStream;

public class DBHelper extends SQLiteOpenHelper {

   public static final String TAG = "DBHelper";
   public static final String DATABASE_NAME = "znb.db";// 数据库名
   public static final int DATABASE_VERSION = 7;// 数据库版本号
   public static final String TABLE_NAME_GOODS = "goods"; //商品表
   public static final String TABLE_NAME_USER = "user"; //用户表
   public static final String TABLE_NAME_ORDER = "order1"; // 订单表 order是关键字
   public static final String TABLE_NAME_PHOTO = "photo";
   private static final String TABLE_NAME_POSTS = "posts";
   public static final String TABLE_NAME_COLLECTION = "collection";
   public static final String TABLE_NAME_CART = "cart";
   public static final String TABLE_NAME_FOOTPRINT = "footprint";
   private static final String TABLE_NAME_LIKES = "likes";
   public static final String TABLE_NAME_NEWS = "news";
   private Context mContext;

   public DBHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
      this.mContext = context;
   }

   //数据库创建时被调用
   @Override
   public void onCreate(SQLiteDatabase db) {
      initTable(db);
      initData(db);
   }

   //数据库升级
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      if (oldVersion < 7) { // 数据库 1 -> 2 升级
         // 删除测试数据
         db.execSQL("delete from " + TABLE_NAME_GOODS);
         db.execSQL("delete from " + TABLE_NAME_PHOTO);
         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_POSTS);
         initTable(db);
      }
   }

   /**
    * 初始化数据表
    *
    * @param db 数据库
    */
   public void initTable(SQLiteDatabase db) {
      // 创建商品表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_GOODS + " (" +
              "goods_id integer primary key autoincrement," +
              "goods_name text," +
              "goods_price double," +
              "goods_freight double," +
              "goods_address text," +
              "goods_unit text," +
              "goods_number integer," +
              "goods_content text" +
              ")");
      // 创建用户表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USER + " (" +
              "_id integer primary key autoincrement," +
              "user_name text," +
              "user_phone text," +
              "user_email text," +
              "user_password text)");
      // 创建订单表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ORDER + " (" +
              "order_id integer primary key autoincrement," +
              "goods_id integer," +
              "goods_name text," +
              "goods_price double," +
              "goods_address text," +
              "goods_content text," +
              "FOREIGN KEY (goods_id) REFERENCES goods(goods_id))");
      // 创建图片表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PHOTO + " (" +
              "img_id integer primary key autoincrement," +
              "goods_img_id  integer," +
              "post_id integer," +
              "image BLOB," +
              "FOREIGN KEY (goods_img_id) REFERENCES goods(goods_id))");
      //发趣淘圈
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_POSTS + " (" +
              "post_id integer primary key autoincrement," +
              "user_id integer," +
              "content text," +
              "username TEXT," +
              "post_type integer," + // 0-动态 1-文章
              "image_paths text," + // 多个图片路径用逗号分隔
              "image BLOB," +
              "create_time datetime default current_timestamp," +
              "FOREIGN KEY (user_id) REFERENCES user(_id))");
      //关注
      String createFollowTable = "CREATE TABLE follow (" +
              "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
              "user_id INTEGER, " +
              "followed_user_id INTEGER, " +
              "FOREIGN KEY (user_id) REFERENCES user(_id), " +
              "FOREIGN KEY (followed_user_id) REFERENCES user(_id)" +
              ")";
      db.execSQL(createFollowTable);
      // 创建收藏表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_COLLECTION + " (" +
              "collection_id integer primary key autoincrement," +
              "goods_id integer," +
              "goods_name text," +
              "goods_price double," +
              "goods_address text," +
              "goods_content text," +
              "FOREIGN KEY (goods_id) REFERENCES goods(goods_id))");

      // 创建购物车表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_CART + " (" +
              "cart_id integer primary key autoincrement," +
              "goods_id integer," +
              "goods_name text," +
              "goods_price double," +
              "goods_address text," +
              "goods_content text," +
              "FOREIGN KEY (goods_id) REFERENCES goods(goods_id))");
      // 创建足迹表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_FOOTPRINT + " (" +
              "footprint_id integer primary key autoincrement," +
              "goods_id integer," +
              "goods_name text," +
              "goods_price double," +
              "goods_address text," +
              "goods_content text," +
              "FOREIGN KEY (goods_id) REFERENCES goods(goods_id))");
      // 创建点赞表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_LIKES + " (" +
              "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
              "user_id INTEGER, " +
              "post_id INTEGER, " +
              "FOREIGN KEY (user_id) REFERENCES user(_id), " +
              "FOREIGN KEY (post_id) REFERENCES posts(post_id)" +
              ")");
      // 创建新闻表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_NEWS + " (" +
              "news_id INTEGER PRIMARY KEY AUTOINCREMENT," +
              "title TEXT," +
              "content TEXT," +
              "image_path TEXT," +
              "publish_time TEXT," +
              "views INTEGER)");
   }

   /**
    * 初始化数据库内部模拟数据(仅在第一次安装时执行)
    *
    * @param db 数据库
    */
   public void initData(SQLiteDatabase db) {
      db.execSQL("insert into " + TABLE_NAME_GOODS +
              " (goods_name , goods_price, goods_freight, goods_address, goods_unit, goods_number, goods_content) " +
              "values" +
              "('四川峨眉山黄瓜', 1.23, 0, '四川峨眉山', '斤', 123, '来自四川雪山之巅的黄瓜。')");
      db.execSQL("insert into " + TABLE_NAME_GOODS +
              " (goods_name , goods_price, goods_freight, goods_address, goods_unit, goods_number, goods_content) " +
              "values" +
              "('东北大米', 3.33, 0, '东北', '斤', 444, '来自东北的大米，煮饭特别香。')");
      db.execSQL("insert into " + TABLE_NAME_GOODS +
              " (goods_name , goods_price, goods_freight, goods_address, goods_unit, goods_number, goods_content) " +
              "values" +
              "('海南香蕉', 9.99, 0, '海南', '斤', 213, '海南香蕉，特别好吃')");
      db.execSQL("insert into " + TABLE_NAME_GOODS +
              " (goods_name , goods_price, goods_freight, goods_address, goods_unit, goods_number, goods_content) " +
              "values" +
              "('新疆又香又甜的哈密瓜', 5.99, 0, '新疆', '斤', 333, '又大又甜，特别好吃')");
      db.execSQL("insert into " + TABLE_NAME_GOODS +
              " (goods_name , goods_price, goods_freight, goods_address, goods_unit, goods_number, goods_content) " +
              "values" +
              "('江苏黄瓜', 1.12, 0, '江苏', '斤', 223, '来自江苏的黄瓜。')");
      Log.d(TAG, "Data inserted into " + TABLE_NAME_GOODS);

      db.execSQL("insert into " + TABLE_NAME_USER +
              " (_id , user_name, user_phone, user_email, user_password) " +
              "values" +
              "(1, '1', '1', '1', '1')");
      db.execSQL("insert into " + TABLE_NAME_USER +
              " (_id , user_name, user_phone, user_email, user_password) " +
              "values" +
              "(2, '2', '2', '2', '2')");
      db.execSQL("insert into " + TABLE_NAME_USER +
              " (_id , user_name, user_phone, user_email, user_password) " +
              "values" +
              "(3, '3', '3', '3', '3')");
      db.execSQL("INSERT INTO " + TABLE_NAME_NEWS + " (title, content, image_path, publish_time, views) VALUES " +
              "('农为体 融为用', '在农业发展的进程中...', '../pic/nongtr.jpg', '2024-05-19', 2704), " +
              "('山为仓 海为廪', '“山为仓 海为廪”体现...', '../pic/sh.jpg', '2024-05-18', 28045), " +
              "('全国乡村人才工作会议在陕西咸阳召开', '全国乡村人才工作会议...', '../pic/dah.jpg', '2024-05-17', 28712), " +
              "('食为天 民为大', '“食为天 民为大”深刻地...', '../pic/shiminda.jpg', '2024-05-17', 12381), " +
              "('民生为大：大食物观实践纪事', '“民生为大：大食物观...', '../pic/mswd.jpg', '2024-05-18', 7954)");
      Log.d(TAG, "Data inserted into " + TABLE_NAME_NEWS);

      // 插入帖子数据
      long postId1 = insertPost(db, 3, "user1", "这是用户3发布的动态", 0);
      long postId2 = insertPost(db, 2, "user2", "这是用户2发布的文章", 1);

      // 插入图片数据
      if (postId1 != -1) {
         insertPhoto(db, postId1, R.mipmap.haimianbaobao);
      }
      if (postId2 != -1) {
         insertPhoto(db, postId2, R.mipmap.paidaxing);
      }
   }

   private long insertPost(SQLiteDatabase db, int userId, String username, String content, int postType) {
      String insertQuery = "INSERT INTO " + TABLE_NAME_POSTS +
              " (user_id, username, content, post_type) " +
              "VALUES (" + userId + ", '" + username + "', '" + content + "', " + postType + ")";
      db.execSQL(insertQuery);
      // 获取最后插入的ID
      Cursor cursor = db.rawQuery("SELECT last_insert_rowid()", null);
      if (cursor.moveToFirst()) {
         long id = cursor.getLong(0);
         cursor.close();
         return id;
      }
      cursor.close();
      return -1;
   }

   private void insertPhoto(SQLiteDatabase db, long postId, int drawableResId) {
      byte[] imageData = getImageBytes(mContext, drawableResId); // 使用保存的Context对象
      String insertQuery = "INSERT INTO " + TABLE_NAME_PHOTO +
              " (post_id, image) " +
              "VALUES (" + postId + ", ?)";
      db.execSQL(insertQuery, new Object[]{imageData});
   }

   private byte[] getImageBytes(Context context, int drawableResId) {
      Bitmap bitmap = BitmapFactory.decodeResource(context.getResources(), drawableResId);
      ByteArrayOutputStream stream = new ByteArrayOutputStream();
      bitmap.compress(Bitmap.CompressFormat.PNG, 100, stream);
      return stream.toByteArray();
   }

   @SuppressLint("Range")
   public String getNewsImagePath(String title) {
      SQLiteDatabase db = this.getReadableDatabase();
      String[] projection = {"image_path"};
      String selection = "title = ?";
      String[] selectionArgs = {title};
      Cursor cursor = db.query(TABLE_NAME_NEWS, projection, selection, selectionArgs, null, null, null);
      String imagePath = null;
      if (cursor.moveToFirst()) {
         imagePath = cursor.getString(cursor.getColumnIndex("image_path"));
      }
      cursor.close();
      return imagePath;
   }
}